﻿using Dapper;

using HBase;
using HBase.db;
using hCommand;
using Net.http;
using System;
using System.Collections.Generic;
using System.Linq;

namespace Persistence.abs
{
    public abstract class AbsCSDServer : AbsExtendsServer, ICSDServer
    {
        public CoreStock getCoreStockByLocId(int locId)
        {
            String sql = "select * from [Core_stock] where  status>-1 and loc_Id=@locId";
            CoreStock cs = readConn().QueryUnique<CoreStock>(sql, new { locId = locId });
            return cs;
        }

        public List<CoreStockDet> getCoreStockByCode(string boxCode)
        {
            string sql = "select a.* from core_stock_det a  where   1=1   and box_code=@boxCode";

            return readConn().Query<CoreStockDet>(sql, new { box_code = boxCode }).ToList();
        }
        public List<CoreStock> getCoreStockByStatus(StockStatus status)
        {
            string sql = "select a.* from Core_stock a  where   1=1   and status=@status";

            return readConn().Query<CoreStock>(sql, new { status = (int)status }).ToList();
        }
        public Page<CoreStock> QueryCoreStockPage(CoreStockDto dto)
        {
            string sql = "select a.* from Core_stock a  where   1=1  ";


            if (dto.stockStatus != 0)
            {
                sql += " and status=@stockStatus";
            }
            else
            {
                sql += " and status >0 ";
            }

            if (!string.IsNullOrEmpty(dto.name))
            {
                dto.name = "%" + dto.name + "%";
                sql += " AND name like @name ";
            }
            if (dto.rareaId != 0)
            {
                sql += " AND d_Item_id = @rareaId ";
            }
            if (dto.locId != 0)
            {
                sql += " AND loc_Id = @locId ";
            }

            if (dto.pkId > 0)
            {
                sql += " AND pk_id = @pkId ";
            }
            else if (dto.pkId < 0)
            {
                dto.pkId = -dto.pkId;
                sql += " AND pk_id <> @pkId ";
            }

            return this.queryPage<CoreStock>(sql, "id desc", dto);
        }

        public List<CoreStockDet> GetListByStockId(long stockId)
        {
            string sql = "select * from core_stock_det where 1=1 and pid=@stockId and status>=0  order by b_pos";
            return readConn().Query<CoreStockDet>(sql, new { stockId = stockId }).ToList();
        }









        public bool UpdatePriorityById(CoreStockDto model)
        {
            string sql = @"UPDATE  core_stock_det SET Priority =@priority WHERE id=@id";
            int a = opConn().Execute(sql, new { id = model.stockDetailId, priority = model.priority });
            if (a >= 1)
            {
                return true;
            }
            return false;
        }




        public Page<CoreStockDet> QueryCoreStockDetailPage(CoreStockDetalDto dto)
        {
            string sql = @"SELECT  *  FROM v_core_stock_det  d      WHERE    d.status< "+ (int)StockDetailStatus.上限;
            if (dto.itemId != 0)
            {
                sql += " and d.item_Id=@itemId ";
            }
            if (!String.IsNullOrEmpty(dto.barCode))
            {
                dto.barCode = "%" + dto.barCode + "%";
                sql += " and d.bar_code like @barCode ";
            }

            if (!String.IsNullOrEmpty(dto.bName))
            {
                dto.bName = "%" + dto.bName + "%";
                sql += " and d.b_name like @bName ";
            }
            if (dto.beginTime != null && dto.beginTime != DateTime.MinValue)
            {
                sql += $" AND d.change_Time like '%{dto.beginTime?.ToString("yyyy-MM-dd")}%' ";
            }
            if (dto.shelfId > 0)
            {
                dto.shelfId *= 100000;
                sql += " AND s.loc_id>@shelfId AND s.loc_id<@shelfId+100000";

            }
            if (dto.storeStatus < 0)
            {
                sql += " and d.status < 0 ";
            }
            else if (dto.storeStatus != 0)
            {
                sql += " and d.status = @storeStatus ";
            }
            else
            {
                sql += " and d.status >0 ";
            }
            if (!string.IsNullOrEmpty(dto.boxCode))
            {
                sql += $" AND d.box_code like '%{dto.boxCode}%' ";
            }
            if (dto.pkId != 0)
            {
                sql += " and d.pk_id = @pkId ";
            }
            if (dto.pid > 0)
            {
                sql += " and d.pid = @pid ";
            }
            if (dto.doiDetId > 0)
            {
                sql += " and d.doi_Det_Id = @doiDetId ";
            }
            return queryPage<CoreStockDet>(sql, "id", dto);
        }
        public List<CoreStockDet> lastCoreStockDetail()
        {
            string sql = @"SELECT   * FROM core_stock_det order by change_time desc LIMIT 0,5";
            return readConn().Query<CoreStockDet>(sql).ToList();
        }

        public List<CoreStockDet> listCoreStockDetail(CoreStockDetalDto dto)
        {
            string sql = "";
            if (dto.itemId != 0)
            {
                sql += " and item_Id=@itemId ";
            }

            if (!String.IsNullOrEmpty(dto.barCode))
            {
                dto.barCode = "%" + dto.barCode + "%";
                sql += " and bar_code like @barCode ";
            }
            if (!String.IsNullOrEmpty(dto.bName))
            {

                dto.bName = "%" + dto.bName + "%";
                sql += " and b_name like @bName ";
            }
            if (dto.storeStatus > 0)
            {
                sql += " and status like @storeStatus ";
            }
            sql += "order by id";
            return readConn().GetAll<CoreStockDet>(sql, dto);
        }





      
        public void changeCsdDooDetId(long dooDetId, string add, string del)
        {
            string sql = "update core_stock_det  set doo_det_id=@dooDetId  where id in @id  ";

            opConn().Execute(sql, new { dooDetId, id = add.Split(",") });
            opConn().Execute(sql, new { dooDetId = 0, id = del.Split(",") });
        }
        //-------------
        public long UpdateDetailStatusStockId(long stockId, int status)
        {
            string sql = @"UPDATE  core_stock_det SET status =@status  WHERE pid=@stockId";
            return opConn().Execute(sql, new { stockId = stockId, status = status });
        }

        public long UpdateDetailStatusStockDetailId(long stockDetailId, int status)
        {
            string sql = @"UPDATE  core_stock_det SET status =@status WHERE id=@stockDetailId";
            return opConn().Execute(sql, new { stockDetailId = stockDetailId, status = status });
        }


        public List<CoreStockDet> getDetail(List<String> barCodes)
        {


            String checkSql = "select * from core_stock_det  where  status>0 and bar_code in @barCodes";

            List<CoreStockDet> res = readConn().Query<CoreStockDet>(checkSql, new { barCodes = barCodes.ToArray() }).ToList();
            return res;
        }

        public List<CoreStockDet> getDetail(long stockId, bool doo)
        {
            String checkSql = "";
            if (doo)
            {
                checkSql = "SELECT d.*,o.do_No item_code FROM `core_stock_det` d left join wh_doo_det dd on d.doo_det_id=dd.id left join wh_doo o on dd.pid=o.id   where d.status>=0 and d.status<=70  and d.pid=@stockId";
            }
            else
            {
                checkSql = "select * from core_stock_det  where  status>=0 and status<=70  and pid =@stockId  order by b_pos";
            }


            List<CoreStockDet> res = readConn().Query<CoreStockDet>(checkSql, new { stockId }).ToList();
            return res;
        }


        public String SaveStock(CoreStockDto coreStock)
        {
            String updateSql = "update core_stock_det set b_pos=@pos ,pid=@stockId  where bar_code=@barCode and status>0 and Status<9";


            long id;
            if (coreStock.id == 0)
            {
                CoreStock stock = new CoreStock();

                stock.status = (int)StockStatus.空闲; 
                stock.createTime = DateTime.Now;
                stock.changeTime = stock.createTime;
                stock.pkTime= stock.createTime;
                stock.boxCode = coreStock.boxCode;
                opConn().InsertNoNull(stock);
                id = stock.id;
            }
            else
            {

                CoreStock old = opConn().GetById<CoreStock>(coreStock.id);
                if (old.status != (int)StockStatus.空闲)
                {
                    return coreStock.id + "托盘状态异常" + old.status;
                }
                id = coreStock.id;

                string sql1 = "update  core_stock_det set pid=0 where pid=@id and Status>0 and  Status<9";
                opConn().Execute(sql1, coreStock);
            }
            for (int i = 0; i < coreStock.barCodes.Count; i++)
            {
                opConn().Execute(updateSql, new { pos = i + 1, stockId = id, barCode = coreStock.barCodes[i] });
            }
            return "保存成功";
        }



        public bool deleteStock(CoreStock cs)
        {
            String sql2 = "select count(*) from core_stock_det  where status<{0} and  pid=@id";

            int cont = opConn().QueryUnique<int>(HUtil.s(sql2, StockDetailStatus.上限), cs);
            if (cont == 0)
            {
                cs.status = (int)StockStatus.直接删除;
                opConn().Update(cs, x=>new { x.status });
                return true;
            }
            return false;
        }






    



        public List<CoreStockDet> inert(int day)
        {
            DateTime dt = DateTime.Now;
            dt = dt.AddDays(-day);

            String sql = "SELECT item_Id, sum(count_db) count_db,min(CREATE_time) CREATE_time FROM `core_stock_det`  where status="+ (int)StockDetailStatus.库存 + "  and  create_Time<@dt GROUP BY item_Id ";
            //
            return readConn().Query<CoreStockDet>(sql, new { dt }).ToList();

        }

        public List<CoreStockDet> getCSDByDoId(long id)
        {
            String sql = "select * from core_stock_det where doo_det_id=@id or doi_det_id=@id";
            List<CoreStockDet> list = readConn().Query<CoreStockDet>(sql, new { id }).ToList();
            return list;
        }
    }
}
